<?php

namespace app\command;

use app\model\OrderModel;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use think\console\Command;
use think\console\Input;
use think\console\Output;

class NewParse extends Command
{
    public $allcells = [];

    protected function configure()
    {
        // 指令配置
        $this->setName('new_parse')
            ->setDescription('the new_parse command');
    }

    protected function execute(Input $input, Output $output)
    {
        try {
            $filename = '20250505.xlsx';
            $filePath = root_path() . '/public/' . $filename;
            $this->getOrderDetails($filePath);
        } catch (\Exception $e) {
            var_dump($e->getTraceAsString());
            var_dump($e->getMessage());
        }       
    }

    // 函数：获取订单号和商品名称
    function getOrderDetails($filePath)
    {
        $orderDetails       = [];
        $spreadsheet        = IOFactory::load($filePath);
        $sheet              = $spreadsheet->getActiveSheet();
        $highestRow         = $sheet->getHighestRow();
        $highestColumn      = $sheet->getHighestColumn();
        $highestColumnIndex = Coordinate::columnIndexFromString($highestColumn);

        for ($row = 1; $row <= $highestRow; $row++) {
            // 获取订单号
            $orderIdCell = $sheet->getCell(Coordinate::stringFromColumnIndex(1) . $row);
            $orderId     = '';
            $is_merge    = $this->isCellMerged($sheet, $row);
            $skip        = 0;
            if ($is_merge !== false) {
                list($start, $end) = Coordinate::rangeBoundaries($is_merge);
                $startRow = $start[1];
                $endRow   = $end[1];
                $skip     = $endRow - $startRow;
                if ($startRow == $row) {
                    // 只在合并单元格的起始行获取订单号
                    $orderId = $orderIdCell->getValue();
                }
            } else {
                // 该单元格不是合并单元格
                $orderId = $orderIdCell->getValue();
            }

            if ($orderId != '') {
                $orderDetails = ['order_sn' => trim($orderId), 'productNames' => []];
                // 获取商品名称
                $productNames = [];
                for ($i = $row; $i <= ($is_merge === false ? $row : $endRow); $i++) {
                    $productNameCell = $sheet->getCell(Coordinate::stringFromColumnIndex(17) . $i); // Q 列对应的列数是 17
                    $productName     = $productNameCell->getValue();
                    if ($productName != '') {
                        $productNames[] = $productName;
                    }
                }
                $orderDetails['productNames']      = $productNames;
                $orderDetails['amount']            = trim($sheet->getCell("C" . $row)->getValue());
                $orderDetails['order_create_time'] = trim($sheet->getCell("G" . $row)->getValue());
                $orderDetails['customer']          = trim($sheet->getCell("I" . $row)->getValue());
                $orderDetails['mobile']            = trim($sheet->getCell("J" . $row)->getValue());
                $orderDetails['address']           = trim($sheet->getCell("K" . $row)->getValue());
                $orderDetails['idcard']           = trim($sheet->getCell("M" . $row)->getValue());
                $orderDetails['old_address']       = $orderDetails['address'];
                $orderDetails['is_combine']        = $is_merge === false ? 0 : 1;
                // 处理表格数据
                $this->dealInfo($orderDetails);
            }

            // 跳过组合单占用的行数
            $row += $skip;
        }
    }

    // 检查当前行是否在合并单元格范围内
    function isCellMerged(Worksheet $worksheet, int $row, int $col = 1)
    {
        $mergedCells = $worksheet->getMergeCells();
        foreach ($mergedCells as $mergedCellRange) {
            list($startCell, $endCell) = Coordinate::rangeBoundaries($mergedCellRange);
            if ($startCell[1] == $endCell[1]) continue;
            if ($startCell[0] == $col) {
                if ($startCell[1] <= $row && $row <= $endCell[1]) {
                    return $mergedCellRange;
                }
            }
        }
        return false;
    }

    public function dealInfo($orderInfo)
    {
        $is_exists = OrderModel::where('order_sn', trim($orderInfo['order_sn']))->find();
        if ($is_exists) {
            if (!$is_exists['customer']) {
                $customer = doubao_parse_name($orderInfo['customer']);
                $is_exists['customer'] = $customer;
            }
            if (!$is_exists['address']) {
                $address = doubao_parse_address($orderInfo['address']);
                $is_exists['address'] = $address;
            }
            if (!$is_exists['idcard']) {
                $is_exists['idcard'] = $orderInfo['idcard'];
            }
            $is_exists->save();
            var_dump($orderInfo['order_sn'] . "===已存在");
            return;
        }
        $containsGoodlife = !empty(array_filter($orderInfo['productNames'], function ($name) {
            return strpos($name, "GOODLIFE") !== false;
        }));
        if ($containsGoodlife) {
            var_dump($orderInfo['order_sn'] . "====GOODLIFE商品");
            return;
        }
        if ($orderInfo['customer'] != '现场取货') {
            $orderInfo['customer']          = doubao_parse_name($orderInfo['customer']);
        }
        // 是否保税
        $is_bonded = !empty(array_filter($orderInfo['productNames'], function ($name) {
            return strpos($name, "（保税）") !== false;
        }));
        if ($is_bonded) {
            $orderInfo['is_bonded'] = 1;
        } else {
            $orderInfo['is_bonded'] = 2;
        }
        if (strpos($orderInfo['address'], "中国") !== false) {
            $orderInfo['type'] = 1;
//            $orderInfo['address'] = tecent_address_parse($orderInfo['address']);
            $orderInfo['address'] = doubao_parse_address($orderInfo['address']);
            var_dump($orderInfo['order_sn'] . '====' . $orderInfo['address']);
        } else {    // 不包含
            $orderInfo['type'] = 2;
        }

        $result = [];
        if ($orderInfo['is_combine'] == 0) {   // 不是组合的情况
            if (stripos($orderInfo['productNames'][0], "组合")) {
                $orderInfo['is_combine'] = 1;
            }
        }
        foreach ($orderInfo['productNames'] as $productName) {
            $result = array_merge($result, $this->parseSexCnt($productName));
        }
        // 过滤空数组
        $result = array_values(array_filter($result));
        if (!$result) return false;

        $goods_type_arr = array_column($result, "goods_type");
        $all_items      = [];
        if (in_array("cell", $goods_type_arr) || in_array("hufu", $goods_type_arr) || in_array("yxnd", $goods_type_arr)) {   // 不全是akg的订单，拆开
            foreach ($result as $item) {
                $tmp_item = $orderInfo;
                $tmp_item['goods_name'] = json_encode($tmp_item['productNames'], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
                unset($tmp_item['productNames']);
                $tmp_item['pink_cnt']   = $item['is_lady'] == false ? 0 : $item['cnt'];
                $tmp_item['blue_cnt']   = $item['is_lady'] == false ? $item['cnt'] : 0;
                $tmp_item['is_lady']    = $item['is_lady'] == false ? 0 : 1;
                $tmp_item['is_combine'] = 0;
                $tmp_item['goods_type'] = $item['goods_type'];
                $all_items[]            = $tmp_item;
            }
            (new OrderModel())->saveAll($all_items);
        } else {    // 全是akg的订单
            $res                      = $this->parseSexCntArr($result);
            $orderInfo['pink_cnt']    = $res['pink_cnt'];
            $orderInfo['blue_cnt']    = $res['blue_cnt'];
            $orderInfo['is_lady']     = $res['is_lady'] == false ? 0 : 1;
            $orderInfo['combine_cnt'] = $this->combineCnt($orderInfo['pink_cnt'], $orderInfo['blue_cnt']);
            $orderInfo['goods_type']  = 'akg';
            $orderInfo['goods_name'] = json_encode($orderInfo['productNames'], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
            unset($orderInfo['productNames']);
            OrderModel::create($orderInfo);
        }
    }

    /**
     * 提取字符串中 男女的数量
     * 商品名：GnAKG女*2 男*1逆龄组合MB，商品数量：1
     * @param $productName
     * @return array
     */
    function extractGenderCounts($productName)
    {
        $femaleCounts = [];
        $maleCounts   = [];

        // 匹配 "女*[数字]" 的模式
        preg_match_all('/女\*(\d+)/', $productName, $femaleMatches);
        if (!empty($femaleMatches[1])) {
            $femaleCounts = array_map('intval', $femaleMatches[1]);
        }

        // 匹配 "男*[数字]" 的模式
        preg_match_all('/男\*(\d+)/', $productName, $maleMatches);
        if (!empty($maleMatches[1])) {
            $maleCounts = array_map('intval', $maleMatches[1]);
        }

        // 计算总数
        $femaleTotal = array_sum($femaleCounts);
        $maleTotal   = array_sum($maleCounts);

        return ['female' => $femaleTotal, 'male' => $maleTotal];
    }

    public function parseSexCntArr($arr)
    {
        $result = [
            'pink_cnt' => 0,
            'blue_cnt' => 0,
            'is_lady'  => false
        ];
        if (count($arr) == 1) { // 非组合
            $result['is_lady'] = $arr[0]['is_lady'];
        }
        foreach ($arr as $k => $v) {
            if ($v['is_lady'] == false) {
                $result['blue_cnt'] += $v['cnt'];
            } else {
                $result['pink_cnt'] += $v['cnt'];
            }
        }
        return $result;
    }

    public function parseSexCnt($str)
    {
        if (stripos($str, '招财猫') !== false) return [[]];
        // 判断是男士还是女士
        if (stripos($str, '女士') !== false || stripos($str, '护肤') !== false) {
            $isLady = true;
        } else {
            $isLady = false;
        }
//        $isLady = stripos($str, '女士') !== false;
        $gender = $isLady ? '女士' : '男士';

        // 判断商品类型
        $issvip = stripos($str, 'SVIP') !== false;
        $isvip  = stripos($str, 'VIP') !== false;
        $ismb   = stripos($str, 'MB') !== false;

        $istwo   = stripos($str, '两瓶') !== false;
        $isfour  = stripos($str, '四瓶') !== false;
        $isthree = stripos($str, '三瓶') !== false;
        $isfive  = stripos($str, '五瓶') !== false;

        // 提取数量
        preg_match('/商品数量：(\d+)/', $str, $matchesForQuantity);
        $quantity = isset($matchesForQuantity[1]) ? $matchesForQuantity[1] : 0;
        if ($issvip) {
            $quantity = $quantity * 5;
        } else if ($isvip) {
            $quantity = $quantity * 3;
        } else if ($istwo) {
            $quantity = $quantity * 2;
        } else if ($isfour) {
            $quantity = $quantity * 4;
        } else if ($isthree) {
            $quantity = $quantity * 3;
        } else if ($isfive) {
            $quantity = $quantity * 5;
        }

        $goods_type = $this->getGoodsType($str);
        if (stripos($str, "组合") !== false) {
            if (strpos($str, "*") !== false) { 
                $cnt_arr = $this->extractGenderCounts($str);
                return [
                    ['is_lady' => false, 'cnt' => $cnt_arr['male'], 'goods_type' => $goods_type],
                    ['is_lady' => true, 'cnt' => $cnt_arr['female'], 'goods_type' => $goods_type]
                ];
            } else {
                return [
                    ['is_lady' => false, 'cnt' => $quantity, 'goods_type' => $goods_type],
                    ['is_lady' => true, 'cnt' => $quantity, 'goods_type' => $goods_type]
                ];
            }
        } else {
            return [['is_lady' => $isLady, 'cnt' => $quantity, 'goods_type' => $goods_type]];
        }
    }

    public function getGoodsType($goods_name)
    {
        if (stripos($goods_name, "GnAKG") !== false) {
            return 'akg';
        } elseif (stripos($goods_name, "GnCell") !== false) {
            return 'cell';
        } elseif (stripos($goods_name, "护肤套盒") !== false) {
            return 'hufu';
        } elseif (stripos($goods_name, "招财猫") !== false) {
            return 'zcm';
        } elseif (stripos($goods_name, "银杏纳豆") !== false) {
            return 'yxnd';
        }
        return 'akg';
    }

    public function combineCnt($pink_cnt, $blue_cnt)
    {
        if ($blue_cnt == 1 && $pink_cnt == 1) return 1;
        if ($blue_cnt == 2 && $pink_cnt == 1) return 2;
        if ($blue_cnt == 4 && $pink_cnt == 1) return 3;
        if ($blue_cnt == 1 && $pink_cnt == 4) return 4;
        if ($blue_cnt == 1 && $pink_cnt == 2) return 5;
        if ($blue_cnt == 2 && $pink_cnt == 2) return 6;
//        if ($blue_cnt == 0 && $pink_cnt == 5) return 7;
        if ($blue_cnt == 3 && $pink_cnt == 1) return 8;
        if ($blue_cnt == 1 && $pink_cnt == 3) return 9;
        if ($blue_cnt == 2 && $pink_cnt == 3) return 10;
        if ($blue_cnt == 3 && $pink_cnt == 2) return 11;
        if ($blue_cnt == 5 && $pink_cnt == 5) return 12;
//        if ($blue_cnt == 0 && $pink_cnt == 4) return 13;
//        if ($blue_cnt == 4 && $pink_cnt == 0) return 14;
        if ($blue_cnt == 3 && $pink_cnt == 3) return 15;
        if ($blue_cnt == 2 && $pink_cnt == 4) return 16;
        if ($blue_cnt == 4 && $pink_cnt == 4) return 17;
//        if ($blue_cnt == 5 && $pink_cnt == 0) return 18;
        if ($blue_cnt == 5 && $pink_cnt == 1) return 19;
//        if ($blue_cnt == 6 && $pink_cnt == 0) return 20;
        return 0;
    }
}